GASでローカルCSVを取り込み、スプレッドシートで請求書を作成する

CSVファイルからデータを処理して、毎月特定の形式の請求書を発行するというケースがあり、Google SpreadsheetでテンプレートをデザインしてGASでスクリプト処理を実行しました。その方法を紹介します。

#JavaScript

#Google Spreadsheet

#Google Apps Script

#バックオフィス

Ito ma

2020.04.23

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

Guten Tag! ベルリンより伊藤です。

特殊な請求書を発行するケースがあり、CSVファイルのデータから請求書を作成する必要がありましたので、スプレッドシート + GASで実装しました。

この投稿を書いている中で次々にバグを見つけて修正できました。アウトプット大切！

はじめに

ネットで「CSVを取り込んでスプレッドシートにデータを入力する」といった情報は調べるといくつか出てきますが、普通にコードをコピペして試してみると動かない場合があります。というのも、GASでは2020年2月より V8 ランタイムが導入され、現在デフォルトで有効になっているため、以前のランタイムを元に書かれたコードではエラーとなる可能性があるからです。もちろんそのままで問題ない場合もありますが、この時期より前に紹介されているコードはランタイムが原因で正しく動作しない可能性があるということをご認識おきくださいませ。（私はこれに気づくまでに丸一日消費しましたので...）

対策としては、以下の方法があります。

a. Migrating scripts to the V8 runtime を元にV8ランタイム対応にコードを更新する（日本語で解説しているページでは Google Apps ScriptのV8 Runtime対応を検証してみた - officeの杜が細かく紹介されていました）

b. プロジェクトで以前のラインタイム（Rhino）を使用するよう切り替える（次の通り、V8の有効・無効はスクリプトエディタのページからいつでも変更可能です）

GASでV8を無効にする

可能ならもちろん a. で進める方が良いでしょうが、V8のバグによりHTML内でファイルのアップロードができない疑いがあり、今回導入する処理を実現するためにはV8を無効にして進めることにしました。

ですので、本記事のスクリプトは Rhino ランタイム（V8無効）で動作確認 しています。

なお、「CSVを取り込んでスプレッドシートにデータを入力する」だけでしたら、下記のページが大変分かりやすく、大部分を参考にさせていただきました。（※こちらもファイルアップロードを使うため、デフォルトのV8では動きません）

参考: 連載目次：GASのダイアログでcsvファイルアップローダーを作ろう

今回やったこと

ユーザは下記の流れで請求書を作成できます。

(1) レートを入力、必要に応じて請求日などを変更する
(2) （任意）「リセット」ボタンをクリックし、既存のレコードをクリアする
(3) 「CSV取り込み」ボタンをクリックする

(4) アップローダのダイアログが開くので、取り込むCSVファイルを選択する

(5) 先ほどの画面に戻りCSVデータが表に貼り付けられるので、「作成する」ボタンをクリック
(6) 新しいシート「YYYY年M月」に請求書が作成される
(7) 作成されたシートを印刷する

作成された請求書のサンプル

ここで使う「作成フォーム」と「テンプレート」のシートをあらかじめ作成しておき、ボタンクリックで行われる処理をスクリプトで実装していきます。

スプレッドシートの準備（非スクリプト）

次の2つのシートを用意します。

作成フォーム

ユーザはこのシートを使用して請求書の作成を行います。

請求書に載る情報（請求日、締日、請求書番号など）は、基本的に手動入力しなくて良いよう、下図のように =TODAY() や =EOMONTH()といった関数を使用しています。（為替レートのみ外部サイトで確認して手動で入力する必要あり）

そして、以下の3つのボタンを用意しています。

[CSV取り込み]: アップローダが開かれCSVファイルを選択すると、シート上にデータが貼り付けされる
[作成する]: シート上の情報を元に、「テンプレート」シートを複製して請求書が作成される
[リセット]: シート上のレートとCSVレコードが消え、請求日を本日にリセットする

ボタンは「図形描画」を挿入して、「スクリプトを割り当て」で上図の青字の通りそれぞれ関数を割り当てています。図形描画はコピペができないようなので、グリッドを参考にしつつ同じサイズのものを3つ作りました。

参考: Google Sheets Button: Run Apps Script With A Single Click

テンプレート

こちらのシートは、請求書作成時にユーザが触ることはありません。

テンプレとなるよう共通部分をすべて入力して書式なども整えておきます。こちらも、Total (EUR) は =M18*Q18 （Unit Price x Qty.）、Margin は =S24*Q25（Subtotal x 5%）など、あらかじめ関数を仕込んでおきます。

また、A4の余白標準でちょうどよく印刷が収まるよう、列幅と範囲も調整しておきます。

※グリッド線は、次の図では分かりやすいように表示させていますが、誤ってグリッド線入りで印刷されないよう実際には表示をオフにしておきます。

請求書テンプレート

「作成する」ボタンが押されると、このシートが複製され、作成日やCSVデータに応じて図のオレンジ部分が更新されます。

スクリプト

大まかな記述の仕方などは、Google App Script 公式のチュートリアルを参考にしました。

リセット

冒頭でグローバル変数としていくつかセル位置を定義しています。レートのセル位置は offset を使って請求日のセル位置を元に範囲を特定しています。

var FORM = '作成フォーム';
var TMPL = 'テンプレート';

var IV_DATE = 'C2';  // 作成フォームの請求日のセル位置
var RC_ROW = 10;     // 作成フォームのレコード開始行
var RC_COL = 1;      // 作成フォームのレコード開始列
var IV_ROW = 20;     // テンプレートのレコード開始行
var IV_COL = 3;      // テンプレートのレコード開始列

/**
 * This method clears the ranges of existing records and currency rate
 * when a user clicks the "Reset" button.
 */
function resetForm() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName(FORM);  
  
  // フォームの既存レコードをクリア
  clearRecords(RC_ROW, RC_COL, sheet);
  
  // 既存のレートをクリア
  var ivDate = sheet.getRange(IV_DATE);
  ivDate.offset(2, 0).clearContent();
  
  // 請求日をデフォルトの本日にリセット
  ivDate.setFormulaR1C1('=TODAY()');
  ivDate.setNumberFormat("yyyy/mm/dd");
}

function clearRecords(row, col, sheet) {  
  sheet.getRange(row, col, sheet.getLastRow(), sheet.getLastColumn()).clearContent();
}

CSV取り込み

スクリプトエディタで main.gs に加えて index.html を用意します。流れとしては、ボタンをクリックしたら、openDialog() からシート上に html のダイアログが開かれ、そこでユーザがファイルを選択して送信すると processForm() が実行されます。

骨子は公式ドキュメントのFormsと冒頭でも紹介したページ（いつも隣にITのお仕事）を参考にしましたので、詳細はそちらから。

/**
 * This method opens Index.html in a dialog which includes a file uploader
 * when a user clicks the "Import CSV" button.
 */
function openDialog() {
  var html = HtmlService.createHtmlOutputFromFile('Index');
  SpreadsheetApp.getUi()
      .showModalDialog(html, 'CSV取り込み');
}

/**
 * This method is called by Index.html when the user uploads a file in a dialog.
 */
function processForm(formObject) {
  var blob = formObject.myFile;
  var csvText = blob.getDataAsString("sjis");
  var values = Utilities.parseCsv(csvText);

  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName(FORM);
  
  // 既存レコードをクリアし、CSVのレコードを貼り付け
  clearRecords(RC_ROW, RC_COL, sheet);
  sheet.getRange(RC_ROW - 1, RC_COL, values.length, values[0].length).setValues(values);  
}

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <script>
      // Prevent forms from submitting.
      function preventFormSubmit() {
        var forms = document.querySelectorAll('form');
        for (var i = 0; i < forms.length; i++) {
          forms[i].addEventListener('submit', function(event) {
            event.preventDefault();
          });
        }
      }
      window.addEventListener('load', preventFormSubmit);

      function handleFormSubmit(formObject) {
        google.script.run
          .withSuccessHandler(google.script.host.close)
          .withFailureHandler(function () {
            alert('アップロードに失敗しました');
            google.script.host.close();
        })
          .processForm(formObject);
       }
    </script>
  </head>
  <body>
    <form id="myForm" onsubmit="handleFormSubmit(this)" enctype="multipart/form-data">
      <input name="myFile" type="file" />
      <input type="submit" value="取り込み" />
    </form>
 </body>
</html>

作成する

ボタンをクリックすると、createInvoice() を実行し、実行確認、テンプレシートの複製、請求日や番号の貼り付け、レコードの貼り付け・計算の修正、印刷用に改行を調整、請求書番号の連番を更新といった流れで処理します。

/**
 * This method creates a new invoice sheet by duplicating a template sheet
 * and by copying and fixing original records on the form sheet,
 * when a user clicks the "Create" button.
 */
function createInvoice() {
  var ss = SpreadsheetApp.getActive();
  var sheetForm = ss.getSheetByName(FORM);
  var sheetTemplate = ss.getSheetByName(TMPL);
  
  var ivDate = sheetForm.getRange(IV_DATE);  // 請求日のセル位置
    
  // 対象の年月（請求日の前月）を取得し、新しい請求書のシート名を定義
  var descDate = addMonths(ivDate.getValue(), -1);
  var descYear = descDate.getFullYear();
  var descMonth = descDate.getMonth() + 1;   // getMonth() returns 0 to 11.
  var newSheetName = descYear + "年" + descMonth + "月";

  // 既存シートの削除確認 or 作成の実行確認をし、キャンセルなら処理を中断
  var cancel = cancelCreate(newSheetName);
  if (cancel) {
    Browser.msgBox('新しい請求書の作成をキャンセルしました。');
    return;
  }
  
  // シートを複製
  dublicateSheet(sheetTemplate, newSheetName);
  
  // 作成したシートにヘッダーを入力
  var sheetNew = ss.getSheetByName(newSheetName);
  setHeader(sheetNew, ivDate, descDate);
  
  // 作成したシートにレコードを整形して貼り付け
  pasteRecords(sheetForm, sheetNew, RC_ROW, RC_COL, IV_ROW, IV_COL);

  // subtotal〜Totalのブロックが印刷1ページ目と2ページ目の改ページをまたぐ場合、調整する
  alignPrint(sheetNew);

  // 請求書番号の連番を更新
  var num = ivDate.offset(1, 0);
  var nextNum = Number(num.getValue()) + 1;
  num.setValue(nextNum);

  Browser.msgBox("作成が完了しました。",Browser.Buttons.OK);
};


/**
 * Check if a sheet with the same year and month already exists
 * and prompts to delete it and continue the process.
 *
 * @param {string} sheetName  a name String of the new sheet.
 * @return {boolean}      true if user chose not to continue the process.
 */
function cancelCreate(sheetName) {
  var ss = SpreadsheetApp.getActive();
  var exSheet = ss.getSheetByName(sheetName);
  if (exSheet != null) {
    // 既存のシートがある場合、削除確認
    var delPrompt = Browser.msgBox("同じ年月のシートが既に存在します。削除して作成し直しますか？",Browser.Buttons.OK_CANCEL);
    if (delPrompt == 'ok') {
      // OKの場合、既存のシートを選択して削除し、処理を続ける
      ss.setActiveSheet(exSheet, true);
      ss.deleteActiveSheet();
      Logger.log('Existing sheet has been deleted.');
      return false;
    } else {
      // そうでない場合、true を返す
      Logger.log('User declined to delete the existing sheet.');
      return true;
    }
  } else {
    var conPrompt = Browser.msgBox("入力した請求日、レート、レコードで請求書を作成してもよろしいですか？",Browser.Buttons.OK_CANCEL);
    if (conPrompt == 'ok') {
      // OK の場合、処理を続ける
      Logger.log('User confirmed to create a new invoice.');
      return false;
    } else {
      // そうでない場合、true を返す
      Logger.log('Invoice creation was canceled by user.');
      return true;
    }
  }
}

/**
 * Dublicate a template sheet and rename the new sheet.
 *
 * @param {sheet} sheet  "template" Sheet to duplicate.
 * @param {string} name  name String of the new sheet.
 */
function dublicateSheet(sheet, name) {  
  var ss = SpreadsheetApp.getActive();

  ss.setActiveSheet(sheet, true);
  var sheetNew = ss.duplicateActiveSheet();
  sheetNew.setName(name);
  sheetNew.setTabColor(null);
};

/**
 * Set a header of the duplicated sheet.
 *
 * @param {sheet} sheet   duplicated Sheet.
 * @param {range} ivDate  a cell Range of input invoide date.
 * @param {date} descDate a Date of the previous month of invoice date.
 */
function setHeader(sheet, ivDate, descDate) {
  // フォームの各入力値の位置を定義
  var ivDue = ivDate.offset(0, 2);
  var ivNum = ivDate.offset(1, 2);
  var curRate = ivDate.offset(2, 0);

  // 作成した新しいシートを選択し、請求日、締切日、請求書番号、レートを順に貼り付け
  sheet.getRange('S10:V10').activate();
  ivDate.copyTo(sheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  sheet.getRange('S11:V11').activate();
  ivDue.copyTo(sheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  
  sheet.getRange('R9:V9').activate();
  ivNum.copyTo(sheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  sheet.getRange('H19:I19').activate();
  curRate.copyTo(sheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  // 利用費の対象期間として請求日前月の年月を指定
  // var descMonth = descDate.toLocaleString("en-US", { month: "short" });  // V8 Runtimeの場合
  var descMonth = shortMonth(descDate);
  var descYear = descDate.getFullYear();

  var desc = sheet.getRange('A18').getValue() + " " + descMonth + " " + descYear;
  sheet.getRange('A18').setValue(desc); 
};

/**
 * Copy records from a "form" sheet and paste accordingly to the new sheet.
 *
 * @param {sheet} sheet1  form Sheet to paste from.
 * @param {sheet} sheet2  new Sheet to paste to.
 * @param {int} r1  First row Number of record to copy.     10
 * @param {int} c1  First column Number of record to copy.   1
 * @param {int} r2  First row Number of record to paste.    20
 * @param {int} c2  First column Number of record to paste.  3
 */
function pasteRecords(sheet1, sheet2, r1, c1, r2, c2) {
  // 新しいシートにレコードを入力する行を追加
  var numRows = sheet1.getLastRow() - r1 + 1; // レコード行数 30-10+1=21
  sheet2.insertRowsAfter(r2, numRows - 1);    // 1行目の下に20行を追加

  // 1行目をコピーして追加した行に貼り付け
  var frRng = sheet2.getRange(r2, 1, 1, sheet2.getMaxColumns());               // コピー元の範囲 20:20 (20,1,1,max)
  var toRng = sheet2.getRange(r2 + 1, 1, numRows - 1, sheet2.getMaxColumns()); // コピー先の範囲 21:40 (21,1,20,max)
  toRng.activate();
  frRng.copyTo(sheet2.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

  // 二次元配列の変数を定義
  var acctDescValues = [];
  var acctUsdValues = [];

  // レコードの行数だけ繰り返す
  for (var i = 0; i < numRows ; ++i) {
    var row1 = r1 + i;
    var row2 = r2 + i;

    // アカウントの説明文
    var acctDescValue = [sheet1.getRange(row1, c1 + 5).getValue()];
    acctDescValues.push(acctDescValue);

    // アカウントの利用費（USD）
    var acctUsdValue = [sheet1.getRange(row1, c1 + 3).getValue()];
    acctUsdValues.push(acctUsdValue);
  }

  // 貼り付け先にそれぞれ値を入力
  var acctDescRng = sheet2.getRange(r2, c2, numRows, 1);
  var acctUsdRng = sheet2.getRange(r2, c2 + 13, numRows, 1);  
  acctDescRng.setValues(acctDescValues);
  acctUsdRng.setValues(acctUsdValues);

  // Unit Price に合計のSUM式を入力
  var unitPrice = sheet2.getRange(r2 - 2, c2 + 10);
  var sumForm =
      "=SUM(R[" + 2 + "]C[" + 6 
      + "]:R[" + (2 + numRows - 1) + "]C[" + 8 + "])";
  unitPrice.setFormulaR1C1(sumForm);

  sheet2.getRange('A1').activate();
}

/**
 * Add rows to display the Total Amount block in one page.
 *
 * @param {sheet} sheet  new Sheet to paste to.
 */
function alignPrint(sheet) {
  // Subtotal が41〜45行目にある場合、Subtotal前に行を追加する
  var subtotalRow = sheet.createTextFinder('Subtotal').findNext().getRow();
  if (subtotalRow > 40 || subtotalRow > 46 ) {
    var addNum = 46 - subtotalRow;       // 2ページ目の1行目から引いた数
    sheet.insertRowsBefore(subtotalRow, addNum);
  }
}

/**
 * Get a short name of the month from date.
 *
 * @param {date} date  original Date.
 * @return {string}    3-digit month name.
 */
function shortMonth(date){
  const monthNames = ["Jan", "Feb", "Mar", "Apr", "May", "Jun",
  "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];
  var monthNum = date.getMonth();
  var monthName = monthNames[monthNum];
  return monthName;
}

/**
 * Add the number of month to date.
 *
 * @param {date} date  original Date.
 * @param {int} months  number of month to add.
 * @return {date}      result date.
 */
function addMonths(date, months) {
    var d = date.getDate();
    date.setMonth(date.getMonth() + months);
    if (date.getDate() != d) {
      date.setDate(0);
    }
    return date;
}

補足

当初、レコードを貼り付ける際に for 文の中で setValue を記述して、一行ずつ値を入力していたのですが、GASでローカルCSVインポート - Qiita を参考に、for文では配列に値を入れ、setValuesでまとめて貼り付けるようにすると、処理が一瞬で終わるようになりました。ただしsetValuesのセル数には限界（251?）があるそうなのでデータ量が増える場合は注意が必要です。

なお、この参考ページでもCSVファイル取り込みが行なわれていますが、ダイアログに使用されているUiApp（UI Service）は現在は廃止されており、代わりに本稿のようにHtmlServiceを使用することが推奨されています。

参考

addMonths(): JavaScript function to add X months to a date - Stack Overflow
shortMonth(): Get the Name of the Current Month in Vanilla JavaScript - Coder Rocket Fuel

余談

冒頭でせっかくセル位置を定義しているんですが、setHeader() における請求日、請求書番号などの貼り付け先は、関数内でベタ書きにしてしまいました。

また、印刷で Total のブロックが途切れないよう調整する alignPrint() は、現状では1,2ページの間の処理しか対応していないので、件数が増えた場合のことは考えていません。

などなど長ーーーく運用する上での不備はありますが、社内利用ということで...

結局使わなかったけど参考メモ

レコードの「012345678901」と「Account Name」から組み合わせて「0123-4567-XXXX Account Name」という値にする処理です。（ここまで調べて書いたのに、よくよく見たらレコード側でできあがった値をそのまま持っていたので不要な処理となりました...）

参考: Google Apps Script(GAS)で文字列を切り出し・抽出(slice,substring,substr)の解説 | AutoWorker〜Google Apps Script(GAS)とSikuliで始めるRPA入門

    // アカウントの説明文
    var acctNum = zeroPad(sheet1.getRange(row1, c1).getValue(), 12);
    var acctName = sheet1.getRange(row1, c1 + 1).getValue();
    var acctDesc = acctNum.slice(0,4) + "-" + acctNum.slice(4,8) + "-XXXX " + acctName;

上記で使われている zeroPad は下記のゼロパディングを参考にしました。

参考: JavaScriptで数値の桁数を合わせる（ゼロパディング）方法 - SMART 開発者のためのウェブマガジン

/**
 * Add zero to increase the length. This is no longer needed.
 *
 * @param {int} NUM  original number.
 * @param {int} LEN  length of the number.
 * @return {string}  zero-added number added.
 */
function zeroPad(NUM, LEN){
  return ( Array(LEN).join('0') + NUM ).slice( -LEN );
}

おわりに

かなり特殊なケースにはなりますが、CSVを使って作るというめんどくさい作業がスクリプトで簡単にできるようになりました！

なお、スクリプトを作っていると、「スプレッドシート上でこの処理はなんていうメソッドなんだろう？」という場面があります。そういう時、もちろんググっても見つけられると思うんですが、マクロを使うと便利です！

今回でいうと例えば、シートの色を変更したい場合に、「マクロを記録」で試しにシートの色を変更し、記録されたコードから setTabColor() を使うことがわかりました。マクロについては、以前GAS初心者としてマクロからスクリプト記述した方法をこちらのブログで紹介しましたので、ぜひご参考ください。

Auf Wiederlesen!